"""
SqlAlchermy filter方法常用过滤条件
"""


import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, ".."))

from sqlalchemy import and_, or_
from orm.demo3 import PositionEnum, CompanyStaff
from conf.mysql_config import SESSION


def search_data():
    # 1. equals
    results = SESSION.query(
        CompanyStaff.user_name,
        CompanyStaff.position).filter(
        CompanyStaff.user_name == '黄丽华')
    # 2. not equals
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(
        CompanyStaff.position != PositionEnum.product.name)
    # 3. like
    results = SESSION.query(
        CompanyStaff.user_name,
        CompanyStaff.position).filter(
        CompanyStaff.user_name.like('李%'))
    # 4. in
    results = SESSION.query(
        CompanyStaff.user_name,
        CompanyStaff.position).filter(
        CompanyStaff.user_name.like('李%'),
        CompanyStaff.position.in_(
            [
                PositionEnum.product.name,
                PositionEnum.ui.name]))
    # 5. not in
    results = SESSION.query(CompanyStaff.user_name,
                            CompanyStaff.position).filter(
        CompanyStaff.user_name.like('李%'), ~CompanyStaff.position.in_([PositionEnum.product.name, PositionEnum.ui.name]))
    results = SESSION.query(CompanyStaff.user_name,
                            CompanyStaff.position).filter(
        CompanyStaff.user_name.like('李%'),
        CompanyStaff.position.not_in([PositionEnum.product.name, PositionEnum.ui.name]))
    # 6. is null
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(CompanyStaff.remark==None)
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(CompanyStaff.remark.is_(None))
    # 7. is not null
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(CompanyStaff.user_name.like('黄%'),
                                                                                  CompanyStaff.remark != None)
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(CompanyStaff.user_name.like('黄%'),
                                                                                  CompanyStaff.remark.isnot(None))
    # 8. and
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(CompanyStaff.user_name.like('黄%'),
                                                                                  CompanyStaff.position == PositionEnum.front_end.name)
    results = SESSION.query(CompanyStaff.user_name, CompanyStaff.position).filter(and_(CompanyStaff.user_name.like('黄%'),
                                                                                       CompanyStaff.position == PositionEnum.front_end.name))
    # 9. or
    results = SESSION.query(CompanyStaff.user_name,
                            CompanyStaff.position).filter(
        CompanyStaff.user_name.like('黄%'), or_(CompanyStaff.position == PositionEnum.product.name,
                                               CompanyStaff.position == PositionEnum.front_end.name))
    for result in results:
        print(result.user_name, result.position.value)


if __name__ == '__main__':
    search_data()
